package com.xxx.springboot;

import org.sqlite.SQLiteConnection;

import java.sql.*;

public class SQLiteConnectionExample {

//    private static String url = "jdbc:sqlite:D:/example.db";
    private static String sqlite_db_file_path = "jdbc:sqlite:D:/test.db";

    private static void testConnectSqlite() {
        try {
            // 加载SQLite JDBC驱动
            Class.forName("org.sqlite.JDBC");
            // 建立连接，这里假设数据库文件名为test.db，位于当前目录下
            Connection connection = DriverManager.getConnection(sqlite_db_file_path);
            System.out.println("成功连接到SQLite数据库");
            // 关闭连接
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private static void testCreateTable() {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection(sqlite_db_file_path);
            Statement statement = connection.createStatement();
            String sql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT," +
                    "age INTEGER)";
            statement.executeUpdate(sql);
            System.out.println("表创建成功");
            statement.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private static void testInsert() {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection(sqlite_db_file_path);
            Statement statement = connection.createStatement();
            String sql = "INSERT INTO users (name, age) VALUES ('John', 30)";
            statement.executeUpdate(sql);
            System.out.println("数据插入成功");
            statement.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private static void testSelect() {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection connection = DriverManager.getConnection(sqlite_db_file_path);
            Statement statement = connection.createStatement();
            String sql = "SELECT * FROM users";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        testConnectSqlite();
        testCreateTable();
        testInsert();
        testSelect();
    }

}

